{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-07-31T14:02:48.912770Z",
     "start_time": "2025-07-31T14:02:48.111482Z"
    }
   },
   "source": "import pandas as pd",
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-07-31T14:02:51.318245Z",
     "start_time": "2025-07-31T14:02:51.313145Z"
    }
   },
   "cell_type": "code",
   "source": "airports =  pd.Series([1,2,3,4,5])",
   "id": "f6da792af974c72c",
   "outputs": [],
   "execution_count": 2
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-07-31T14:16:15.586250Z",
     "start_time": "2025-07-31T14:16:15.577737Z"
    }
   },
   "cell_type": "code",
   "source": [
    "for value in airports:\n",
    "    print(value)"
   ],
   "id": "a321b1317d49a829",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n"
     ]
    }
   ],
   "execution_count": 19
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-07-31T14:18:15.069491Z",
     "start_time": "2025-07-31T14:18:15.059311Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = pd.DataFrame([\n",
    "    ['zhangkun','28','China'],\n",
    "    ['JackMa','45','USA'],\n",
    "    ['Mao','83','China'],\n",
    "    ['Shelock','42','UK']],\n",
    "    columns=['Name','Age','City'])\n"
   ],
   "id": "f68ef0e316a17727",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "       Name Age   City\n",
       "0  zhangkun  28  China\n",
       "1    JackMa  45    USA"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>City</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>zhangkun</td>\n",
       "      <td>28</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JackMa</td>\n",
       "      <td>45</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 23
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-07-31T14:18:39.260381Z",
     "start_time": "2025-07-31T14:18:39.231254Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 查看案例信息\n",
    "df.head(2)\n",
    "df.tail(2)\n",
    "# 遍历信息\n",
    "for column in df.columns:\n",
    "    print(column)\n",
    "# 展示基础信息\n",
    "df.shape\n",
    "# 展示详细信息\n",
    "df.info()"
   ],
   "id": "174597807bef9aee",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4 entries, 0 to 3\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   Name    4 non-null      object\n",
      " 1   Age     4 non-null      object\n",
      " 2   City    4 non-null      object\n",
      "dtypes: object(3)\n",
      "memory usage: 228.0+ bytes\n"
     ]
    }
   ],
   "execution_count": 25
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-07-31T14:29:18.185104Z",
     "start_time": "2025-07-31T14:29:18.175598Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 如何切片\n",
    "df.iloc[0:5,[0,1,2]]"
   ],
   "id": "104f5125ce1ce2e8",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "       Name Age   City\n",
       "0  zhangkun  28  China\n",
       "1    JackMa  45    USA\n",
       "2       Mao  83  China\n",
       "3   Shelock  42     UK"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>City</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>zhangkun</td>\n",
       "      <td>28</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JackMa</td>\n",
       "      <td>45</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mao</td>\n",
       "      <td>83</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Shelock</td>\n",
       "      <td>42</td>\n",
       "      <td>UK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 47
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-07-31T14:54:09.738281Z",
     "start_time": "2025-07-31T14:54:09.717862Z"
    }
   },
   "cell_type": "code",
   "source": [
    "coffee = pd.read_csv('../warmup-data/coffee.csv')\n",
    "coffee.info()\n",
    "# 处理错误数据"
   ],
   "id": "8614692d9c938a48",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14 entries, 0 to 13\n",
      "Data columns (total 3 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   Day          14 non-null     object\n",
      " 1   Coffee Type  14 non-null     object\n",
      " 2   Units Sold   14 non-null     int64 \n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 468.0+ bytes\n"
     ]
    }
   ],
   "execution_count": 58
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-01T01:29:12.766278Z",
     "start_time": "2025-08-01T01:29:12.627852Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 链接数据库\n",
    "import pandas as pd\n",
    "import pymysql as mysql\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "host='localhost'\n",
    "port=3306\n",
    "user='root'\n",
    "password='zhang3916'\n",
    "database='zto'\n",
    "\n",
    "engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8' % (user, password, host, port, database))\n"
   ],
   "id": "a40c72c7cbfeb452",
   "outputs": [],
   "execution_count": 9
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-01T03:17:53.759867Z",
     "start_time": "2025-08-01T03:15:17.534261Z"
    }
   },
   "cell_type": "code",
   "source": [
    "sqlCustom = 'select * from zto.CRMSpecialAPolicy'\n",
    "sqlAllBasic = 'select * from zto.CustomSiteAll'\n",
    "dfCustom = pd.read_sql(sqlCustom,engine)\n",
    "dfAll = pd.read_sql(sqlAllBasic,engine)"
   ],
   "id": "4fddf5f9f7755ba3",
   "outputs": [],
   "execution_count": 21
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-01T03:19:50.873636Z",
     "start_time": "2025-08-01T03:19:46.459216Z"
    }
   },
   "cell_type": "code",
   "source": "CustomBill = pd.merge(dfAll,dfCustom,left_on='BelongCustom',right_on='BelongCustom',how='inner')",
   "id": "a8b763bda417429c",
   "outputs": [],
   "execution_count": 22
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-01T03:22:22.084329Z",
     "start_time": "2025-08-01T03:22:21.155548Z"
    }
   },
   "cell_type": "code",
   "source": "CustomBill.to_csv('custom-bill.csv',index=False,encoding='utf-8')",
   "id": "44b00a7aa210763",
   "outputs": [],
   "execution_count": 24
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
